package com.example.demo.Services.impl;

import com.example.demo.Services.GSServices;
import com.example.demo.entiy.Bgd;
import com.example.demo.entiy.XM;
import com.example.demo.utils.MyUtils;
import com.example.demo.utils.SqlHelper;
import com.sun.org.apache.bcel.internal.generic.NEW;

import org.springframework.stereotype.Component;
import org.w3c.dom.ls.LSInput;
import org.apache.log4j.Logger;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

@Component
public class GsServicesImpl implements GSServices {
    Connection conn;
    Statement st;
    ResultSet rs;
    PreparedStatement ps;
    MyUtils myUtils=new MyUtils();
    SqlHelper sqlHelper=new SqlHelper();
    private final Logger log = Logger.getLogger(this.getClass());
    @Override
    public List<Bgd> selectAll(String start, String end,String crptname) {
        List<Bgd> li=new LinkedList<>();
        conn=sqlHelper.getConnection();
        String sql="select a.idx as aidx,a.*,b.WRKSTA,f.* from td_tj_bhk(nolock) a " +
                " left join TB_TJ_WRKSTA(nolock) b on  a.wrkstaidx=b.idx left join " +
                "  td_tj_dgn f on a.idx=f.bhkidx " +
                "   where a.bhkdat >= ? and a.bhkdat < ? and a.crptnam=?" +
                "    and a.cmptag>1 order by a.bhkdat desc";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(3,crptname);
            ps.setString(1,start);
            ps.setString(2,end);
            rs=ps.executeQuery();
            li=myUtils.RsToList(rs);
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(crptname+"------------"+e.getMessage());
            //e.printStackTrace();
        }
        return li;
    }

    @Override
    public List<Bgd> selByNameOrBhkCod(String username,String name, String bhkcod) {
        List<Bgd> li=new LinkedList<>();
        conn=sqlHelper.getConnection();
        String sql="select a.idx as aidx,a.*,b.WRKSTA,f.* from td_tj_bhk(nolock) a" +
                " left join TB_TJ_WRKSTA(nolock) b on  a.wrkstaidx=b.idx left join" +
                "  td_tj_dgn(nolock) f on a.idx=f.bhkidx" +
                "   where (a.empnam like '%"+name+"%' or a.bhkcod ='"+bhkcod+"')  and a.crptnam='"+username+"'" +
                "    and a.cmptag>1 order by a.bhkdat desc";
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            li=myUtils.RsToList(rs);
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(bhkcod+"------------"+e.getMessage());
        }

        return li;
    }

    @Override
    public List<XM> selectXm() {
        List<XM> li=new LinkedList<>();
        conn=sqlHelper.getConnection();
        String sql="select * from tb_tj_bsnssrt(nolock) order by rptsrtidx,num";
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()){
                XM xm=new XM();
                xm.setXmNam(rs.getString("bsnssrt"));
                xm.setIdx(rs.getString("idx"));
                li.add(xm);
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(e.getMessage());
        }
        return li;
    }

    @Override
    public String YCxmNam(String idx) {
        String nam="";
        conn=sqlHelper.getConnection();
        String sql="select * from tb_tj_bsnssrt(nolock) where idx='"+idx+"'";
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            if (rs.next()){
                nam=rs.getString("bsnssrt");
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(idx+"------------"+e.getMessage());
        }

        return nam;
    }

    @Override
    public List<Bgd> selYcxm(String emptnam, String start, String end, String idx) {
        List<Bgd> li= new LinkedList<>();
        conn=sqlHelper.getConnection();
        String sql="select  d.idc,d.wrknum,d.dpt,d.bhkcod,d.empnam,d.bhkdat,f.bhkrsl,d.brth,f.mhkadv,f.dgnadv," +
                "d.idx as idx,d.sex,d.age,d.bhksrtidx from" +
                " td_tj_bhk(nolock) d,td_tj_dgn(nolock) f " +
                " where d.idx=f.bhkidx and d.bhkdat>='"+start+"' and d.bhkdat<'"+end+"' and d.cmptag>1 and d.crptnam = '"+emptnam+"'" +
                "  and d.idx in" +
                "  (select distinct mtbidx from td_tj_bhksub_old(nolock) c left join tb_tj_bsnssrtitmrlv(nolock) a  on a.bhkitmidx=c.bhkitmidx  \n" +
                "  where c.rgltag=0 and a.bsnssrtidx='"+idx+"' )";
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()){
                Bgd bgd=new Bgd();
                bgd.setIdc(rs.getString("idc"));
                bgd.setWrknum(rs.getString("wrknum"));
                bgd.setDpt(rs.getString("dpt"));
                bgd.setSex(rs.getString("sex"));
                bgd.setAge(rs.getInt("age"));
                bgd.setIdx(rs.getString("idx"));
                bgd.setTjbm(rs.getString("bhkcod"));
                bgd.setName(rs.getString("empnam"));
                bgd.setBirth( rs.getTimestamp("brth").toString().substring(0,10));
                bgd.setJy(rs.getString("dgnadv"));
                bgd.setMhkadv(rs.getString("mhkadv"));
                bgd.setBhksrtidx(rs.getString("bhksrtidx"));
                //bgd.setEmpidc(rs.getString("empidx"));
                bgd.setTjjg(rs.getString("bhkrsl"));
                if(rs.getString("bhkdat")!=null && rs.getString("bhkdat")!="") {
                    bgd.setBhkdat(rs.getString("bhkdat").substring(0,10));
                }else {
                    bgd.setBhkdat("未体检");
                }


                //bgd.setBhkzt(rs.getString("cmptag"));

                li.add(bgd);
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(idx+"------------"+e.getMessage());
        }

        return li;
    }

    @Override
    public List<Bgd> selWWc(String enpnam, String start, String end) {
        List<Bgd> li=new LinkedList<>();
        String sql="select a.idx as aidx,a.*,b.* from td_tj_bhk(nolock) a left join td_tj_dgn(nolock) b\n" +
                " on a.idx=b.bhkidx where a.bhkdat >= '"+start+"' and a.bhkdat < '"+end+"' \n" +
                " and a.crptnam='"+enpnam+"'  and a.cmptag=0";
        conn=sqlHelper.getConnection();
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()) {
                Bgd bgd=new Bgd();
                bgd.setIdx(rs.getString("aidx"));
                bgd.setTjbm(rs.getString("bhkcod"));
                bgd.setName(rs.getString("empnam"));
                bgd.setBirth( rs.getTimestamp("brth").toString().substring(0,10));
                bgd.setAge(rs.getInt("age"));
                bgd.setIdc(rs.getString("idc"));
                bgd.setDpt(rs.getString("dpt"));
                bgd.setSex(rs.getString("sex"));
                bgd.setWrknum(rs.getString("wrknum"));
                if(rs.getString("bhkdat")!=null && rs.getString("bhkdat")!="") {
                    bgd.setBhkdat(rs.getString("bhkdat").substring(0,10));
                }else {
                    bgd.setBhkdat("未体检");
                }
                bgd.setBhkzt(rs.getString("cmptag"));
                bgd.setTjjg(rs.getString("bhkrsl"));
                bgd.setMhkadv(rs.getString("mhkadv"));
                bgd.setJy(rs.getString("dgnadv"));
                li.add(bgd);
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(enpnam+" 未检测信息  ------------"+e.getMessage());
        }
        return li;
    }

    @Override
    public String selWWC2(String idx) {
        String wwcxm="";
        conn=sqlHelper.getConnection();
        String sql="select B.GRPNAM,* from TS_TJ_FLWCNT_old(nolock) AS A,TB_TJ_GRP(nolock) AS B\n" +
                " where A.bhkmtbidx='"+idx+"' and   A.BHKGRPIDX=B.IDX \n" +
                " and (b.ndspre<>1 or b.grpnam='采血' or b.grpnam='采便' or b.grpnam='采尿' or b.grpnam='采片')\n" +
                "  and a.fnstag<>1";
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()){
                wwcxm+=rs.getString("grpnam")+",";
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(idx+"------------"+e.getMessage());
        }
        return wwcxm;
    }
}
